package com.supermap.rootstock.data.jdbc;

import com.supermap.rootstock.common.core.constant.CommonConstants;
import com.supermap.rootstock.common.core.utils.R;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 数据库连接
 * @date 2019年09月26 9:06
 * @email 1301584033@qq.com
 */
@Controller
@Slf4j
@RequestMapping("/jdbc")
public class JDBCConnection {
	@GetMapping("/getConnection")
	@ResponseBody
	@SneakyThrows
	public R getConnection(Integer dsId, String ip, Integer prot, String sname, String username, String userpwd,String tablename) {
		Connection conn = null;// 创建一个数据库连接
		Statement stmt = null;//查询语句
		ResultSet rs= null;
		String sql="";
		Map<Object,Object> map = new HashMap<>();
		List<Object> list=new ArrayList<>();
		try {
			// 1.加载数据库驱动
			if (dsId != null) {
				if (dsId == 1) {
					//mysql
					Class.forName("com.mysql.cj.jdbc.Driver");
					conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + prot + "/" + sname + "", "" + username + ""
						, "" + userpwd + "");
					stmt = conn.createStatement();
					sql="SELECT table_name FROM information_schema.TABLES  WHERE table_schema = '"+sname+"'  AND table_type = 'base table' ";
					rs=stmt.executeQuery(sql);
					while (rs.next()){
						list.add(rs.getObject("table_name"));
					}
				}else if (dsId == 2) {
					//oracle
					Class.forName("oracle.jdbc.driver.OracleDriver");
					conn = DriverManager.getConnection("jdbc:oracle:thin:@" + ip + ":" + prot + ":" + sname + "", "" + username + ""
						, "" + userpwd + "");
					sql="select table_name from all_tables WHERE owner='"+sname+"' ";
					rs=stmt.executeQuery(sql);
					while (rs.next()){
						list.add(rs.getObject("table_name"));
					}
				}else if (dsId == 3) {
					//SqlSever
					Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
					conn = DriverManager.getConnection("jdbc:sqlserver://" + ip + ":" + prot + ";databaseName=" + sname + "", "" + username + ""
						, "" + userpwd + "");
					sql="select name from sysobjects where xtype='"+sname+"'";
					rs=stmt.executeQuery(sql);
					while (rs.next()){
						list.add(rs.getObject("name"));
					}
				}else if (dsId == 4) {
					//pgsql
					Class.forName("org.postgresql.Driver");
					conn = DriverManager.getConnection("jdbc:postgresql://" + ip + ":" + prot + "/" + sname + "", "" + username + ""
						, "" + userpwd + "");
					sql="select tablename from pg_tables where schemaname = '"+sname+"'";
					rs=stmt.executeQuery(sql);
					while (rs.next()){
						list.add(rs.getObject("tablename"));
					}
				}else{
					return new R().setCode(CommonConstants.FAIL).setMsg("连接失败，占不支持其他数据库!");
				}

			}else{
				return  new R().setCode(CommonConstants.FAIL).setMsg("连接失败，请检查信息是否完整！");
			}
		} catch (Exception e) {
			log.error("连接错误",e);
			e.printStackTrace();
			return  new R().setCode(CommonConstants.FAIL).setMsg("连接失败，请检查数据库配置及相关运行环境是否有误！");
		}
		if(conn!=null){
			if(dsId==1){
				sql="select * from \n" +
					"(select count(1) as countNum from sys_log) t2\n" +
					"inner join \n" +
					"(SELECT date_format(create_time,'%Y-%m-%d- %H:%i:%s')as  ctime,\n" +
					"date_format(update_time,'%Y-%m-%d- %H:%i:%s') as utime\n" +
					"FROM\n" +
					"INFORMATION_SCHEMA. TABLES\n" +
					"WHERE\n" +
					"TABLE_SCHEMA = '"+sname+"'\n" +
					"and TABLE_NAME='"+tablename+"') t1 on 1=1";
			}else{
				conn.close();
				stmt.close();
				rs.close();
				return new R().setCode(CommonConstants.FAIL).setMsg("查询失败,目前只兼容mysql系统查询!");
			}
			rs=stmt.executeQuery(sql);
			while (rs.next()){
				map.put("countNum",rs.getObject("countNum"));
				map.put("ctime",rs.getObject("ctime"));
				map.put("utime",rs.getObject("utime"));
			}
			conn.close();
			stmt.close();
			rs.close();
			if(Strings.isBlank(tablename)){
				return new R().setData(list).setMsg("连接成功!");
			}else{
				return new R().setData(map).setMsg("查询成功!");
			}
		}else {
			return  new R().setCode(CommonConstants.FAIL).setMsg("连接失败，请检查信息是否填写正确！");
		}
	}

}
